using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

namespace SqlTools.SchemaReader
{
	public class Database : DbObject
	{
		private DbCollection<Table> _tables;
		private DbCollection<View> _views;
		private DbCollection<StoredProcedure> _storedProcedures;
		private DbCollection<Default> _defaults;
		private DbCollection<Check> _checks;
		private DbCollection<ForeignKey> _foreignKeys;
		private DbCollection<PrimaryKey> _primaryKeys;

		private DbCollection<DbObject> _objects;

		protected Dictionary<byte, string> _types;

		protected bool _loadSProcs;

		public Database() : this("") { }

		/// <summary>
		/// Creates an instance of a new database with the specified name, but does not load MetaData.
		/// </summary>
		/// <param name="name">The name of the database.  Used internally only and does not effect the connection.</param>
		public Database(string name)
			: base(name, -1)
		{
			this._tables = new DbCollection<Table>();
			this._views = new DbCollection<View>();
			this._storedProcedures = new DbCollection<StoredProcedure>();
			this._defaults = new DbCollection<Default>();
			this._checks = new DbCollection<Check>();
			this._foreignKeys = new DbCollection<ForeignKey>();
			this._primaryKeys = new DbCollection<PrimaryKey>();

			this._types = new Dictionary<byte, string>();

			this._objects = new DbCollection<DbObject>();

			this._loadSProcs = true;
		}

		/// <summary>
		/// A list of all the tables in a database
		/// </summary>
		public DbCollection<Table> Tables
		{
			get { return _tables; }
		}

		/// <summary>
		/// A list of all the views in a database
		/// </summary>
		public DbCollection<View> Views
		{
			get { return _views; }
		}

		/// <summary>
		/// A list of all the stored procedures in a database
		/// </summary>
		public DbCollection<StoredProcedure> StoredProcedures
		{
			get { return _storedProcedures; }
		}

		/// <summary>
		/// Gets or sets a value indicating whether stored procedure 
		/// definitions should be loaded
		/// </summary>
		public bool LoadStoredProcedures
		{
			get { return _loadSProcs; }
			set { _loadSProcs = value; }
		}

		/// <summary>
		/// A list of all the defaults for every column in every table in the database
		/// </summary>
		internal DbCollection<Default> Defaults
		{
			get { return _defaults; }
		}

		/// <summary>
		/// A list of all the check constraints for every column in every table in the database
		/// </summary>
		internal DbCollection<Check> Checks
		{
			get { return _checks; }
		}

		/// <summary>
		/// A list of every foreign key in every table in database
		/// </summary>
		internal DbCollection<ForeignKey> ForeignKeys
		{
			get { return _foreignKeys; }
		}

		/// <summary>
		/// A list of every primary key in every table in database
		/// </summary>
		internal DbCollection<PrimaryKey> PrimaryKeys
		{
			get { return _primaryKeys; }
		}

		/// <summary>
		/// A list of every single object from sysobjects in this database
		/// </summary>
		internal DbCollection<DbObject> Objects
		{
			get { return _objects; }
		}

		/// <summary>
		/// Loads all the MetaData for the entire database
		/// </summary>
		/// <remarks>This method does not load system tables or views</remarks>
		/// <param name="connectionString">The connection string to use to connect to the database</param>
		public void LoadMetadata(string connectionString)
		{
			using ( SqlConnection conn = new SqlConnection(connectionString) )
			{
				try
				{
					conn.Open();

					LoadTypes(conn);
					ProcessObjects(conn);
					ProcessColumns(conn);
					ProcessForeignKeys(conn);
					ProcesIndexes(conn);
					ProcessIndexKeys(conn);
					ProcessConstraints(conn);
					ProcessComments(conn);
				}
				finally
				{
					if ( conn.State == ConnectionState.Open )
					{
						conn.Close();
					}
				}
			}
		}

		/// <summary>
		/// Loads the text names for all the data types used in this database
		/// </summary>
		/// <param name="conn"></param>
		private void LoadTypes(SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand("SELECT * FROM systypes", conn);

			SqlDataReader sdr = cmd.ExecuteReader();

			while ( sdr.Read() )
			{
				byte xtype = (byte)sdr["xtype"];
				short usertype = (short)sdr["xusertype"];
				string name = sdr["name"].ToString();

				if ( (short)xtype == usertype )
					_types.Add(xtype, name);
			}
			sdr.Close();
		}

		/// <summary>
		/// Loads all the non-system objects from the sysobjects table
		/// </summary>
		/// <param name="conn">An existing, OPEN SqlConnection</param>
		private void ProcessObjects(SqlConnection conn)
		{
			// retrieve list of db objects, excluding system tables
			SqlCommand cmd = new SqlCommand("SELECT * FROM sysobjects WHERE xtype <> 'S' ORDER BY xtype, type", conn);
			cmd.CommandType = CommandType.Text;

			SqlDataReader sdr = cmd.ExecuteReader();

			while ( sdr.Read() )
			{
				string name = sdr["name"].ToString();
				int id = (int)sdr["id"];
				int parent_obj = (int)sdr["parent_obj"];

				DbObject obj = null;

				string xtype = sdr["xtype"].ToString().Trim();

				switch ( xtype )
				{
					case "C":	//C = CHECK constraint
						Check check = new Check(name, id, parent_obj);
						_checks.Add(check);
						obj = check;
						break;
					case "D":	//D = Default or DEFAULT constraint
						Default def = new Default(name, id, parent_obj);
						_defaults.Add(def);
						obj = def;
						break;
					case "F":	//F = FOREIGN KEY constraint
						ForeignKey foreignKey = new ForeignKey(name, id);
						_foreignKeys.Add(foreignKey);
						obj = foreignKey;
						break;
					case "L":	//L = Log
						// future
						break;
					case "FN":	//FN = Scalar function
						// future
						break;
					case "IF":	//IF = Inlined table-function
						// future
						break;
					case "P":	//P = Stored procedure
						StoredProcedure sproc = new StoredProcedure(name, id);
						_storedProcedures.Add(sproc);
						obj = sproc;
						break;
					case "PK":	//PK = PRIMARY KEY constraint (type is K)
						PrimaryKey prikey = new PrimaryKey(name, id, null);
						_primaryKeys.Add(prikey);
						obj = prikey;
						break;
					case "RF":	//RF = Replication filter stored procedure 
						// future
						break;
					case "S":	//S = System table
						break;
					case "TF":	//TF = Table function
						// future
						break;
					case "TR":	//TR = Trigger
						// future
						break;
					case "U":	//U = User table
						Table table = new Table(name, id);
						_tables.Add(table);
						obj = table;
						break;
					case "UQ":	//UQ = UNIQUE constraint (type is K)
						break;
					case "V":	//V = View
						View view = new View(name, id);
						_views.Add(view);
						obj = view;
						break;
					case "X":	//X = Extended stored procedure
						// future
						break;
					default:
						break;
				}

				if ( obj != null )
					_objects.Add(obj);
			}

			sdr.Close();
		}

		/// <summary>
		/// Loads the information for every foreign key in the database
		/// </summary>
		/// <param name="conn">An existing, OPEN SqlConnection</param>
		private void ProcessForeignKeys(SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand("SELECT * FROM sysforeignkeys", conn);

			SqlDataReader sdr = cmd.ExecuteReader();

			while ( sdr.Read() )
			{
				int constid = (int)sdr["constid"];	// fk id
				int fkeyid = (int)sdr["fkeyid"];	// fk table
				int rkeyid = (int)sdr["rkeyid"];	// pk table
				short fkey = (short)sdr["fkey"];	// fk column
				short rkey = (short)sdr["rkey"];	// pk column

				ForeignKey fk = _foreignKeys.RetrieveByID(constid);

				if ( fk != null )
				{
					Table refTable = _tables.RetrieveByID(rkeyid);

					fk.ReferenceTable = refTable;
					if ( refTable != null )
					{
						Column col = refTable.Columns.RetrieveByID(rkey);
						fk.ReferenceColumn = col;

						//refTable.ForeignKeys.Add(fk);
					}

					// add the fkey to the table
					Table fkTable = _tables.RetrieveByID(fkeyid);

					fk.ForeignKeyTable = fkTable;
					if ( fkTable != null )
					{
						Column col = fkTable.Columns.RetrieveByID(fkey);
						fk.ForeignKeyColumn = col;

						// don't add it twice
						//if ( fk.ForeignKeyTable != fk.ReferenceTable )
						    fkTable.ForeignKeys.Add(fk);

						if ( col != null )
							col.IsForeignKey = true;
					}
				}
			}
			sdr.Close();
		}

		/// <summary>
		/// Loads all the indexes for each table in the database, including primary keys.
		/// </summary>
		/// <param name="conn">An existing, OPEN SqlConnection</param>
		private void ProcesIndexes(SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand("SELECT * FROM sysindexes", conn);

			SqlDataReader sdr = cmd.ExecuteReader();

			while ( sdr.Read() )
			{
				int id = (int)sdr["id"];	// table id
				string name = ( sdr["name"] == DBNull.Value ? "" : sdr["name"].ToString() ); // object name
				short indid = (short)sdr["indid"];	// index id

				Table table = _tables.RetrieveByID(id);

				if ( table == null )
					continue;

				Index index = null;

				DbObject obj = _objects[name];
				if ( obj != null )
				{
					switch ( obj.Type )
					{
						case DbObjectType.PrimaryKey:
							PrimaryKey pk = obj as PrimaryKey;
							pk.IndexID = indid;
							index = pk;
							table.PrimaryKey = pk;
							break;
					}
				}
				else
				{
					// regular index
					index = new Index(name, indid, table);
					index.IndexID = indid;
				}

				// set other index props here

				table.Indexes.Add(index);
			}
			sdr.Close();
		}

		/// <summary>
		/// Loads all the columns related to each index
		/// </summary>
		/// <param name="conn">An existing, OPEN SqlConnection</param>
		private void ProcessIndexKeys(SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand("SELECT * FROM sysindexkeys ORDER BY id, indid", conn);

			SqlDataReader sdr = cmd.ExecuteReader();

			Table table = null;
			Index index = null;
			while ( sdr.Read() )
			{
				int id = (int)sdr["id"];	// table id
				short indid = (short)sdr["indid"];	// index id
				short colid = (short)sdr["colid"];	// column id

				if ( table == null || table.ID != id )
				{
					table = _tables.RetrieveByID(id);
					index = null;
				}

				if ( table == null ) continue;

				if ( index == null || index.IndexID != indid )
				{
					index = table.Indexes.RetrieveByIndexID(indid);
				}

				if ( index == null ) continue;

				index.Table = table;

				Column col = table.Columns.RetrieveByID(colid);

				if ( col == null ) continue;

				index.Columns.Add(col);

				if ( index.Type == DbObjectType.PrimaryKey )
					col.IsPrimaryKey = true;
			}
			sdr.Close();
		}

		/// <summary>
		/// Loads all the columns for each table in the database
		/// </summary>
		/// <param name="conn">An existing, OPEN SqlConnection</param>
		private void ProcessColumns(SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand("SELECT * FROM syscolumns ORDER BY id", conn);
			cmd.CommandType = CommandType.Text;

			SqlDataReader sdr = cmd.ExecuteReader();

			while ( sdr.Read() )
			{
				string name = sdr["name"].ToString();
				int id = (int)sdr["id"];
				byte xtype = (byte)sdr["xtype"];
				short colid = (short)sdr["colid"];
				short prec = -1;
				if ( sdr["prec"] != DBNull.Value )
				{
					prec = (short)sdr["prec"];
				}
				int scale = -1;
				if ( sdr["scale"] != DBNull.Value )
				{
					scale = (int)sdr["scale"];
				}

				short length = (short)sdr["length"];

				byte status = (byte)sdr["status"];

				bool identity = ( ( status & 128 ) == 128 );

				bool computed = ( (int)sdr["iscomputed"] == 1 );
				bool output = ( (int)sdr["isoutparam"] == 1 );
				bool nullable = ( (int)sdr["isnullable"] == 1 );

				DbObject obj = _objects.RetrieveByID(id);

				if ( obj != null )
				{
					switch ( obj.Type )
					{
						case DbObjectType.Table:
							Column tcol = new Column(name, colid);
							tcol.Length = length;
							tcol.DataType = GetTypeName(xtype);
							tcol.Scale = scale;
							tcol.Precision = prec;
							tcol.IsComputed = computed;
							tcol.IsNullable = nullable;
							tcol.IsIdentity = identity;

							Table table = obj as Table;
							if ( table != null )
							{
								table.Columns.Add(tcol);
							}
							break;
						case DbObjectType.View:
							Column vcol = new Column(name, colid);
							vcol.Length = length;
							vcol.DataType = GetTypeName(xtype);
							vcol.Scale = scale;
							vcol.Precision = prec;
							vcol.IsComputed = computed;
							vcol.IsNullable = nullable;
							vcol.IsIdentity = identity;

							View view = obj as View;
							if ( view != null )
							{
								view.Columns.Add(vcol);
							}
							break;
						case DbObjectType.StoredProcedure:
							Parameter par = new Parameter(name, colid);
							par.Length = length;
							par.DataType = GetTypeName(xtype);
							par.Scale = scale;
							par.Precision = prec;
							par.IsComputed = computed;
							par.IsNullable = nullable;
							par.IsOutput = output;

							StoredProcedure sproc = obj as StoredProcedure;
							if ( sproc != null )
							{
								sproc.Parameters.Add(par);
							}

							break;
					}
				}
			}
			sdr.Close();

		}

		/// <summary>
		/// Loads all the comments from the database, including default, check, and stored procedures texts
		/// </summary>
		/// <param name="conn">An existing, OPEN SqlConnection</param>
		private void ProcessComments(SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand("SELECT * FROM syscomments ORDER BY id, number, colid", conn);

			SqlDataReader sdr = cmd.ExecuteReader();

			while ( sdr.Read() )
			{
				int id = (int)sdr["id"];
				short number = (short)sdr["number"];
				short colid = (short)sdr["colid"];
				string text = sdr["text"].ToString();

				DbObject obj = _objects.RetrieveByID(id);

				if ( obj != null )
				{
					switch ( obj.Type )
					{
						case DbObjectType.Check:
							Check check = obj as Check;
							if ( check != null )
							{
								if ( colid > 1 )
									check.Text += text;
								else
									check.Text = text;
							}
							break;
						case DbObjectType.Default:
							Default def = obj as Default;
							if ( def != null )
							{
								if ( colid > 1 )
									def.Text += text;
								else
									def.Text = text;
							}
							break;
						case DbObjectType.StoredProcedure:
							if ( !_loadSProcs )
								continue;

							StoredProcedure sproc = obj as StoredProcedure;
							if ( sproc != null )
							{
								if ( colid > 1 )
									sproc.Text += text;
								else
									sproc.Text = text;
							}
							break;
						case DbObjectType.View:
							View view = obj as View;
							if ( view != null )
							{
								if ( colid > 1 )
									view.Text += text;
								else
									view.Text = text;
							}
							break;
					}
				}
			}
			sdr.Close();
		}

		/// <summary>
		/// Loads all the constraints for each column, including primary keys, foreign keys, checks, and defaults
		/// </summary>
		/// <param name="conn">An existing, OPEN SqlConnection</param>
		private void ProcessConstraints(SqlConnection conn)
		{
			SqlCommand cmd = new SqlCommand("SELECT * FROM sysconstraints", conn);

			SqlDataReader sdr = cmd.ExecuteReader();

			while ( sdr.Read() )
			{
				int constid = (int)sdr["constid"];	// constraint id
				int id = (int)sdr["id"];			// table id
				short colid = (short)sdr["colid"];	// column id

				if ( colid != 0 ) // no table constraints
				{
					DbObject obj = _objects.RetrieveByID(constid);

					if ( obj != null )
					{
						Table table = null;
						Column col = null;
						switch ( obj.Type )
						{
							case DbObjectType.ForeignKey:
								break;
							case DbObjectType.PrimaryKey:
								break;
							case DbObjectType.Check:
								Check check = obj as Check;
								if ( check != null )
								{
									table = _tables.RetrieveByID(check.TableID);
									if ( table != null )
									{
										col = table.Columns.RetrieveByID(colid);
										check.Column = col;

										col.Check = check;
									}
								}
								break;
							case DbObjectType.Default:
								Default def = obj as Default;
								if ( def != null )
								{
									table = _tables.RetrieveByID(def.TableID);
									if ( table != null )
									{
										col = table.Columns.RetrieveByID(colid);
										def.Column = col;

										col.Default = def;
									}
								}
								break;
						}
					}
				}
			}
			sdr.Close();
		}

		/// <summary>
		/// Returns a textual type name for a given byte value
		/// </summary>
		/// <param name="xType">The byte value representing a datatype</param>
		/// <returns>The textual type name</returns>
		private string GetTypeName(byte xType)
		{
			if ( _types.ContainsKey(xType) )
				return _types[xType];

			return "";
		}

		/// <summary>
		/// Returns the type of this object instance.
		/// </summary>
		public override DbObjectType Type
		{
			get { return DbObjectType.Database; }
		}

		/// <summary>
		/// Saves the schema represented by this object to XML.
		/// </summary>
		/// <param name="doc">An existing XmlDocument with a defined document element</param>
		/// <param name="xmlParent">The parent element that will contain this objects
		/// schema</param>
		public override void SaveXml(XmlDocument doc, XmlNode xmlParent)
		{
			XmlElement xmlDatabase = CreateXmlElement(doc, xmlParent);

			_tables.SaveXml(doc, xmlDatabase);
			_views.SaveXml(doc, xmlDatabase);
			_storedProcedures.SaveXml(doc, xmlDatabase);
			_foreignKeys.SaveXml(doc, xmlDatabase);

			// save types
			XmlElement xmlTypes = doc.CreateElement("Types");
			xmlDatabase.AppendChild(xmlTypes);

			foreach ( KeyValuePair<byte, string> kvp in _types )
			{
				XmlElement xmlType = doc.CreateElement("Type");
				xmlTypes.AppendChild(xmlType);

				xmlType.Attributes.Append(doc.CreateAttribute("name")).Value = kvp.Value;
				xmlType.Attributes.Append(doc.CreateAttribute("id")).Value = kvp.Key.ToString();
			}
		}

		/// <summary>
		/// Saves the schema of this database to the specified file
		/// </summary>
		/// <param name="filename">The name of the file to which the schema will be saved</param>
		/// <remarks>The specified file will be overwritten</remarks>
		public void Save(string filename)
		{
			XmlDocument doc = new XmlDocument();

			SaveXml(doc, doc);

			doc.Save(filename);
		}

		/// <summary>
		/// Loads database schema from the XML passed in
		/// </summary>
		/// <param name="xml">A fully qualified schema in XML format with a single root element for the database</param>
		public void LoadXml(string xml)
		{
			XmlDocument doc = new XmlDocument();

			doc.LoadXml(xml);

			LoadXml(doc.DocumentElement, this);
		}

		/// <summary>
		/// Loads the database schema from the specified XML file
		/// </summary>
		/// <param name="filename">The name of the file from which to load the schema</param>
		public void Load(string filename)
		{
			XmlDocument doc = new XmlDocument();

			doc.Load(filename);

			LoadXml(doc.DocumentElement, this);
		}

		/// <summary>
		/// Loads the schema from the specified root XML element
		/// </summary>
		/// <param name="el">The Database XML element that is the root of the entire schema</param>
		public void LoadXml(XmlElement el)
		{
			LoadXml(el, null);
		}

		/// <summary>
		/// Loads the schema from the specified root XML element
		/// </summary>
		/// <param name="el">The Database XML element that is the root of the entire schema</param>
		/// <param name="db">This parameter should be null</param>
		public override void LoadXml(XmlElement el, Database db)
		{
			base.LoadXml(el, db);

			XmlNodeList nodes = el.GetElementsByTagName(_tables.XmlElementName);
			if ( nodes.Count > 0 )
				_tables.LoadXml(nodes[0] as XmlElement, this);

			nodes = el.GetElementsByTagName(_views.XmlElementName);
			if ( nodes.Count > 0 )
				_views.LoadXml(nodes[0] as XmlElement, this);

			nodes = el.GetElementsByTagName(_storedProcedures.XmlElementName);
			if ( nodes.Count > 0 )
				_storedProcedures.LoadXml(nodes[0] as XmlElement, this);

			nodes = el.GetElementsByTagName(_foreignKeys.XmlElementName);
			if ( nodes.Count > 0 )
				_foreignKeys.LoadXml(nodes[0] as XmlElement, this);

			nodes = el.GetElementsByTagName("Types");
			if ( nodes.Count > 0 )
			{
				XmlElement xmlTypes = nodes[0] as XmlElement;

				foreach ( XmlNode node in xmlTypes.ChildNodes )
				{
					if ( node.NodeType == XmlNodeType.Element )
					{
						XmlElement xmlType = node as XmlElement;

						if ( xmlType != null && xmlType.Name == "Type" )
						{
							string name = xmlType.GetAttribute("name");
							string key = xmlType.GetAttribute("id");

							byte id = 0;

							if ( byte.TryParse(key, out id) )
							{
								_types.Add(id, name);
							}
						}
					}
				}
			}
		}
	}
}
